package com.sinosoft.system.primary.dao.imp;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.sinosoft.system.primary.dao.SysUserDao;
import com.sinosoft.system.primary.entity.SysUser;

   /**
    * @ClassName SysUserDaoJdbc实现类
    * @date 2017-1-11 
    * @author shangxp
    */  
@Repository
public class SysUserDaoJdbc implements SysUserDao {
	private static Logger log = Logger.getLogger(SysUserDaoJdbc.class);
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	/**
	 * @param sysUser实体类
	 * @return 新增数量  int类型
	 * @author shangxp
	 */
	@Override
	public int insertSysUser(SysUser sysUser) {
		String sqlStr = "insert into sys_user ( "
							+" id ,"	
							+" username ,"	
							+" password ,"	
							+" realname ,"	
							+" nickname ,"	
							+" birthday ,"	
							+" cellphone ,"	
							+" email ,"	
							+" telephone ,"	
							+" location ,"	
							+" headimg ,"	
							+" introduce ,"	
							+" remark ,"	
							+" regdate ,"	
							+"is_del) values( "
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" 0 )";
		log.debug(sqlStr+"\n"+sysUser.toString());
		try{
			Object[] args = {
					sysUser.getId(),
					sysUser.getUsername(),
					sysUser.getPassword(),
					sysUser.getRealname(),
					sysUser.getNickname(),
					sysUser.getBirthday(),
					sysUser.getCellphone(),
					sysUser.getEmail(),
					sysUser.getTelephone(),
					sysUser.getLocation(),
					sysUser.getHeadimg(),
					sysUser.getIntroduce(),
					sysUser.getRemark(),
					sysUser.getRegdate()
			};
			return jdbcTemplate.update(sqlStr, args);
		}catch(Exception e){
			log.error("数据添加异常 SQL："+sqlStr+"\n"+sysUser.toString()+e.getMessage());
		}
		return 0;
	}

	@Override
	public int updateSysUser(SysUser sysUser) {
		String sqlStr = "update sys_user set "
									+" id = ? , "
									+" username = ? , "
									+" password = ? , "
									+" realname = ? , "
									+" nickname = ? , "
									+" birthday = ? , "
									+" cellphone = ? , "
									+" email = ? , "
									+" telephone = ? , "
									+" location = ? , "
									+" headimg = ? , "
									+" introduce = ? , "
									+" remark = ? , "
									+" regdate = ?  "
									+" where id = ?";
		log.debug(sqlStr+"\n"+sysUser.toString());
		try{
			Object[] args = {
					sysUser.getId(),
					sysUser.getUsername(),
					sysUser.getPassword(),
					sysUser.getRealname(),
					sysUser.getNickname(),
					sysUser.getBirthday(),
					sysUser.getCellphone(),
					sysUser.getEmail(),
					sysUser.getTelephone(),
					sysUser.getLocation(),
					sysUser.getHeadimg(),
					sysUser.getIntroduce(),
					sysUser.getRemark(),
					sysUser.getRegdate(),
					sysUser.getId()
			};
			return jdbcTemplate.update(sqlStr, args);
		}catch(Exception e){
			log.error("数据更新异常 SQL："+sqlStr+"\n"+sysUser.toString()+e.getMessage());
		}
		return 0;
	}

	@Override
	public int deleteSysUser(int id) {
		String sqlStr = "update sys_user set is_del = 1  where id = ?";
		try{
			Object[] args = {
					id
			};
			return jdbcTemplate.update(sqlStr, args);
		}catch(Exception e){
			log.error("数据删除异常"+sqlStr+"\nID="+id+"\n"+e.getMessage());
		}	return 0;
	}

	@Override
	public SysUser selectSysUser(int id) {
		String sqlStr = "select * from sys_user where is_del =0 and id = ?";
		log.debug(sqlStr+"\nID="+id);
		try{
			Object[] args = {
				id
			};
			return jdbcTemplate.queryForObject(sqlStr, args, new SysUserMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr+"\nID="+id+"\n"+e.getMessage());
		}	return null;
	}

	@Override
	public SysUser selectSysUser(List<String> queryKeys,List<Object> queryValues) {
		StringBuffer sqlStr = new StringBuffer("select * from sys_user where is_del =0 " );
		Object[] args = null;
		if(queryKeys!=null && !queryKeys.isEmpty()){	
			args = new Object[queryKeys.size()];
			for(int i=0;i<queryKeys.size();i++){
				sqlStr.append(" and "+queryKeys.get(i)+" = ? ");
				args[i] = queryValues.get(i);
			}
		}
		log.debug(sqlStr.toString());
		try{
			return jdbcTemplate.queryForObject(sqlStr.toString(), args, new SysUserMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr.toString()+"\n"+e.getMessage());
		}
		return null;
	}

	@Override
	public int selectCountSysUser() {
		String sqlStr = "select count(1) from sys_user where is_del =0 ";
		log.debug(sqlStr);
		try{
			return jdbcTemplate.queryForObject(sqlStr, Integer.class);
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr+"\n"+e.getMessage());
		}
		return 0;
	}

	@Override
	public List<SysUser> selectLimitSysUser(int start, int size) {
		String sqlStr = "select ";
					sqlStr+=" id ,";
					sqlStr+=" username ,";
					sqlStr+=" password ,";
					sqlStr+=" realname ,";
					sqlStr+=" nickname ,";
					sqlStr+=" birthday ,";
					sqlStr+=" cellphone ,";
					sqlStr+=" email ,";
					sqlStr+=" telephone ,";
					sqlStr+=" location ,";
					sqlStr+=" headimg ,";
					sqlStr+=" introduce ,";
					sqlStr+=" remark ,";
					sqlStr+=" regdate ";
			sqlStr+=" from sys_user where is_del =0  order by id desc limit ?,?";
		log.debug(sqlStr);
		try{
		Object[] args = {
				start,
				size
		};
			return jdbcTemplate.query(sqlStr, args, new SysUserMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr+"\n"+e.getMessage());
		}
		return null;
	}

	@Override
	public List<SysUser> selectAllSysUser() {
		String sqlStr = "select ";
					sqlStr+=" id ,";
					sqlStr+=" username ,";
					sqlStr+=" password ,";
					sqlStr+=" realname ,";
					sqlStr+=" nickname ,";
					sqlStr+=" birthday ,";
					sqlStr+=" cellphone ,";
					sqlStr+=" email ,";
					sqlStr+=" telephone ,";
					sqlStr+=" location ,";
					sqlStr+=" headimg ,";
					sqlStr+=" introduce ,";
					sqlStr+=" remark ,";
					sqlStr+=" regdate ";
		 sqlStr+=" from sys_user where is_del =0 order by id desc ";
		log.debug(sqlStr);
		try{
			return jdbcTemplate.query(sqlStr, new SysUserMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr+"\n"+e.getMessage());
		}
		return null;
	}

	@Override
	public int selectCountSysUser(List<String> queryKeys,List<Object> queryValues) {
		StringBuffer sqlStr = new StringBuffer("select count(1) from sys_user where is_del = 0 ");
		Object[] args = null;
		if(queryKeys!=null && !queryKeys.isEmpty()){	
			args = new Object[queryKeys.size()];
			for(int i=0;i<queryKeys.size();i++){
				sqlStr.append(" and "+queryKeys.get(i)+" = ? ");
				args[i] = queryValues.get(i);
			}
		}		
		log.debug(sqlStr.toString());
		try{
			return jdbcTemplate.queryForObject(sqlStr.toString(), args,Integer.class);
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr.toString()+"\n"+e.getMessage());
		}
		return 0;
	}

	@Override
	public List<SysUser> selectLimitSysUser(List<String> queryKeys,List<Object> queryValues,int start,int size) {
		StringBuffer sqlStr = new StringBuffer("select ");
					sqlStr.append(" id ,");
					sqlStr.append(" username ,");
					sqlStr.append(" password ,");
					sqlStr.append(" realname ,");
					sqlStr.append(" nickname ,");
					sqlStr.append(" birthday ,");
					sqlStr.append(" cellphone ,");
					sqlStr.append(" email ,");
					sqlStr.append(" telephone ,");
					sqlStr.append(" location ,");
					sqlStr.append(" headimg ,");
					sqlStr.append(" introduce ,");
					sqlStr.append(" remark ,");
					sqlStr.append(" regdate ");
			sqlStr.append(" from sys_user where is_del =0 " );
		Object[] args = null;
			if(queryKeys!=null && !queryKeys.isEmpty()){	
				args = new Object[queryKeys.size()+2];
				for(int i=0;i<queryKeys.size();i++){
					sqlStr.append(" and "+queryKeys.get(i)+" = ? ");
					args[i] = queryValues.get(i);
				}
			}
		if(args==null){
			args = new Object[]{start,size};
		}else{
			args[queryKeys.size()] = start;
			args[queryKeys.size()+1] = size;
		}
		sqlStr.append(" order by id desc ");
		sqlStr.append(" limit ?,?");
		log.debug(sqlStr.toString());
		try{
			return jdbcTemplate.query(sqlStr.toString(), args, new SysUserMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr.toString()+"\n"+e.getMessage());
		}
		return null;
	}

	@Override
	public List<SysUser> selectAllSysUser(List<String> queryKeys,List<Object> queryValues) {
		StringBuffer sqlStr = new StringBuffer("select ");
					sqlStr.append(" id ,");
					sqlStr.append(" username ,");
					sqlStr.append(" password ,");
					sqlStr.append(" realname ,");
					sqlStr.append(" nickname ,");
					sqlStr.append(" birthday ,");
					sqlStr.append(" cellphone ,");
					sqlStr.append(" email ,");
					sqlStr.append(" telephone ,");
					sqlStr.append(" location ,");
					sqlStr.append(" headimg ,");
					sqlStr.append(" introduce ,");
					sqlStr.append(" remark ,");
					sqlStr.append(" regdate ");
			sqlStr.append("from sys_user where is_del =0 " );
		Object[] args = null;
			if(queryKeys!=null && !queryKeys.isEmpty()){	
				args = new Object[queryKeys.size()];
				for(int i=0;i<queryKeys.size();i++){
					sqlStr.append(" and "+queryKeys.get(i)+" = ? ");
					args[i] = queryValues.get(i);
				}
			}
		sqlStr.append(" order by id desc ");
		log.debug(sqlStr.toString());
		try{
			return jdbcTemplate.query(sqlStr.toString(), args, new SysUserMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr.toString()+"\n"+e.getMessage());
		}
		return null;
	}

	protected class SysUserMapper implements RowMapper<SysUser> {
		@Override
		public SysUser mapRow(ResultSet arg0, int arg1) throws SQLException {
			SysUser sysUser = new SysUser();
			if(arg0!=null){
					sysUser.setId(arg0.getInt("id"));
					sysUser.setUsername(arg0.getString("username"));
					sysUser.setPassword(arg0.getString("password"));
					sysUser.setRealname(arg0.getString("realname"));
					sysUser.setNickname(arg0.getString("nickname"));
					sysUser.setBirthday(arg0.getString("birthday"));
					sysUser.setCellphone(arg0.getString("cellphone"));
					sysUser.setEmail(arg0.getString("email"));
					sysUser.setTelephone(arg0.getString("telephone"));
					sysUser.setLocation(arg0.getString("location"));
					sysUser.setHeadimg(arg0.getString("headimg"));
					sysUser.setIntroduce(arg0.getString("introduce"));
					sysUser.setRemark(arg0.getString("remark"));
					sysUser.setRegdate(arg0.getString("regdate"));
				}
			return sysUser;
		}  
	}
}

